﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace LabHelper
{
    /// <summary>
    /// SQLConn 的摘要说明。
    /// </summary>
    public class SQLExec
    {
        private static string GetSqlConnString()
        {
            string strSqlConnstring = "Data Source=8A93A3F837F34CD\\SQLEXPRESS;Initial Catalog=LabSys;Integrated Security=True;Pooling=False";
            return strSqlConnstring;
        }

        /// <summary>
        /// 执行sql数据查询
        /// </summary>
        /// <param name="strSQL">数据查询字符串</param>
        /// <returns></returns>
        public static bool ExecuteSql(string strSQL)
        {
            SqlConnection MyConn = new SqlConnection(GetSqlConnString());
            SqlCommand MyComm = new SqlCommand(strSQL, MyConn);
            try
            {
                MyConn.Open();
                MyComm.ExecuteNonQuery();
                return true;
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                MyComm.Dispose();
                MyConn.Close();
            }

        }


        /// <summary>
        /// 出错的调用
        /// </summary>
        /// <param name="strSQL"></param>
        /// <returns></returns>
        public static SqlDataReader ExecuteSqlForDataReader(string strSQL)
        {
            SqlDataReader MyReader = null;
            SqlConnection MyConn = new SqlConnection(GetSqlConnString());
            try
            {
                MyConn.Open();
                SqlCommand MyComm = new SqlCommand(strSQL, MyConn);
                MyReader = MyComm.ExecuteReader();
                MyReader.Read();

                return MyReader;

            }
            catch (System.Data.SqlClient.SqlException e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                MyConn.Close();
            }

        }

        /// <summary>
        /// 执行sql数据操作
        /// </summary>
        /// <param name="strSQL">数据操作字符串</param>
        /// <returns>所查询的数据集</returns>
        public static DataSet ExecuteSqlForDataSet(string strSQL)
        {
            SqlConnection MyConn = new SqlConnection(GetSqlConnString());
            try
            {
                MyConn.Open();

                SqlDataAdapter MyAdapter = new SqlDataAdapter(strSQL, MyConn);
                DataSet ds = new DataSet();
                MyAdapter.Fill(ds);
                return ds;
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                MyConn.Close();
            }
        }

        public static SqlDataAdapter ExecuteSqlForAdapter(string strSQL)
        {
            SqlConnection MyConn = new SqlConnection(GetSqlConnString());
            try
            {
                MyConn.Open();

                SqlDataAdapter MyAdapter = new SqlDataAdapter(strSQL, MyConn);
                return MyAdapter;
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                MyConn.Close();
            }
        }
        /*/// <summary>
        /// 保存数据到数据库
        /// </summary>
        /// <param name="UserName"></param>
        /// <param name="Time"></param>
        /// <param name="Title"></param>
        /// <param name="Type"></param>
        /// <param name="Picture"></param>
        /// <returns>新项的 NewsId</returns>
        public  static int ExecuteSql(string UserName,string Time,string Title ,string Type,string Picture)
        {
            SqlConnection MyConn = new SqlConnection(GetSqlConnString());
            SqlCommand MyCmd = new SqlCommand("pro1", MyConn);
            //指出SQl语句操作的类型是存储过程
            MyCmd.CommandType = CommandType .StoredProcedure ;
            //给存储过程添加参数
            SqlParameter ParameterUserName = new SqlParameter ("@UserName",SqlDbType .VarChar,50);
            ParameterUserName .Value = UserName;
            MyCmd.Parameters .Add (ParameterUserName);
            SqlParameter ParameterTime = new SqlParameter ("@Time",SqlDbType.DateTime ,8);
            ParameterTime .Value = Time;
            MyCmd.Parameters.Add (ParameterTime );
            SqlParameter ParameterTitle = new SqlParameter ("@Title",SqlDbType.VarChar ,50);
            ParameterTitle .Value = Title ;
            MyCmd.Parameters .Add (ParameterTitle );
            SqlParameter ParameterType = new SqlParameter ("@Type",SqlDbType .VarChar ,50);
            ParameterType .Value = Type;
            MyCmd.Parameters .Add (ParameterType );
            SqlParameter ParammeterPicture = new SqlParameter ("@Picture",SqlDbType.VarChar ,50);
            ParammeterPicture .Value = Picture;
            MyCmd.Parameters .Add (ParammeterPicture );
            SqlParameter ParameterKey = new SqlParameter ("@Key",SqlDbType.Int,4);
            //指出此参数为输出参数
            ParameterKey.Direction = ParameterDirection.Output ;
            MyCmd.Parameters .Add (ParameterKey );
            try
            {
                MyConn.Open();
                //执行存储过程
                MyCmd.ExecuteNonQuery();
                //接收存储过程的返回参数
                int Key = (int) ParameterKey .Value;
                return Key;
				
            }
            catch(SqlException e)
            {				
                throw new Exception(e.Message);
            }
            finally
            {
                MyCmd.Dispose();
                MyConn.Close();
            }
			

        } */
        /// <summary>
        /// 
        /// </summary>
        /// <param name="strSQL"></param>
        /// <param name="startindex"></param>
        /// <param name="pagesize"></param>
        /// <param name="tablename"></param>
        /// <returns></returns>

        public static DataSet ExecuteSqlForDataSetPageing(string strSQL, int startindex, int pagesize, string tablename)
        {
            SqlConnection MyConn = new SqlConnection(GetSqlConnString());
            try
            {
                MyConn.Open();

                SqlDataAdapter MyAdapter = new SqlDataAdapter(strSQL, MyConn);
                DataSet ds = new DataSet();
                MyAdapter.Fill(ds, startindex, pagesize, tablename);
                return ds;
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                MyConn.Close();
            }
        }
        public static int ExecuteSqlForScalar(string strSQL)
        {
            int cnt = -1;
            SqlConnection MyConn = new SqlConnection(GetSqlConnString());
            SqlCommand objComm = new SqlCommand(strSQL, MyConn);
            MyConn.Open();
            cnt = (int)objComm.ExecuteScalar();
            MyConn.Close();

            return cnt;
        }
    }
}
